   
/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package rrs;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

public class Report {

private static String totalSales="null";

private static String totalItems="null";

private static String totalTransactions="null";

private static String totalSalesReturned="null";

private static String totalItemsReturned="null";

private static String totalTransactionsReturned="null";

private static String totalFraudulentReturned="null";


public static String getTranRecord(java.util.Date rDate){
	
//Convert java.util.Date to java.sql.Date
java.util.Date reportDate = new java.util.Date();
long t = rDate.getTime();
reportDate = new java.sql.Date(t);

//Query string for TotalSales
String sqlTotalSales="select sum(purchasedPrice*QUANTITY) from TransactionItem a,Transaction b where a.receiptID=b.receiptID and TRANSACTIONDATE-to_date('"+reportDate+"','yyyy-MM-dd')<=1 and TRANSACTIONDATE-to_date('"+reportDate+"','yyyy-MM-dd')>=0";

//Query string for TotalItems
String sqlTotalItems="select sum(QUANTITY) from TransactionItem a,Transaction b where a.receiptID=b.receiptID and TRANSACTIONDATE-to_date('"+reportDate+"','yyyy-MM-dd')<=1 and TRANSACTIONDATE-to_date('"+reportDate+"','yyyy-MM-dd')>=0";

//Query string for TotalTransactions
String sqlTotalTransactions="select count(*) from Transaction where TRANSACTIONDATE-to_date('"+reportDate+"','yyyy-MM-dd')<=1 and TRANSACTIONDATE-to_date('"+reportDate+"','yyyy-MM-dd')>=0";

//Query string for TotalSalesReturned
String sqlTotalSalesReturned="select sum(purchasedPrice*QUANTITY)from TransactionItem a,Transaction b where a.receiptID=b.receiptID and TRANSACTIONDATE-to_date('"+reportDate+"','yyyy-MM-dd')<=1 and TRANSACTIONDATE-to_date('"+reportDate+"','yyyy-MM-dd')>=0 and b.transactionType='Return'";

//Query string for TotalItemsReturned
String sqlTotalItemsReturned="select sum(QUANTITY) from TransactionItem a,Transaction b where a.receiptID=b.receiptID and TRANSACTIONDATE-to_date('"+reportDate+"','yyyy-MM-dd')<=1 and TRANSACTIONDATE-to_date('"+reportDate+"','yyyy-MM-dd')>=0 and TRANSACTIONTYPE='Return'";

//Query string for TotalTransactionsReturned
String sqlTotalTransactionsReturned="select count(*) from Transaction where TRANSACTIONDATE-to_date('"+reportDate+"','yyyy-MM-dd')<=1 and TRANSACTIONDATE-to_date('"+reportDate+"','yyyy-MM-dd')>=0 and transactionType='Return'";

//Query string for TotalFraudulentReturned
String sqlTotalFraudulentReturned="select count(*) from Transaction where TRANSACTIONDATE-to_date('"+reportDate+"','yyyy-MM-dd')<=1 and TRANSACTIONDATE-to_date('"+reportDate+"','yyyy-MM-dd')>=0 and transactionType='Fraudulent'";

//Execute Query string
try {
   
Statement stmt = DBInterface.dbConnOpen().createStatement();

ResultSet set=stmt.executeQuery(sqlTotalSales);
set.next();
totalSales=set.getString(1);

set=stmt.executeQuery(sqlTotalItems);
set.next();
totalItems=set.getString(1);

set=stmt.executeQuery(sqlTotalTransactions);
set.next();
totalTransactions=set.getString(1);

set=stmt.executeQuery(sqlTotalSalesReturned);
set.next();
totalSalesReturned=set.getString(1);

set=stmt.executeQuery(sqlTotalItemsReturned);
set.next();
totalItemsReturned=set.getString(1);

set=stmt.executeQuery(sqlTotalTransactionsReturned);
set.next();
totalTransactionsReturned=set.getString(1);

set=stmt.executeQuery(sqlTotalFraudulentReturned);
set.next();
totalFraudulentReturned=set.getString(1);

set=stmt.executeQuery(sqlTotalFraudulentReturned);
set.next();
totalFraudulentReturned=set.getString(1);

stmt.close();
DBInterface.dbConnClose();
}

catch (SQLException e) {

totalSales=e.toString();

}

//Create return String
return(totalSales+"<>"+totalItems+"<>"+totalTransactions+"<>"+totalSalesReturned+"<>"+

totalItemsReturned+"<>"+totalTransactionsReturned+"<>"+totalFraudulentReturned);

}

}
